package pl.koziolekweb.szkola.MWDerbyNetClient;

// MW-DerbyNetClient/DerbyNetClient.java
 
/*
 * File derbyclient.jar should be in teh classpath
 * when running (but it is not needed for compilation)
 *
 * Compile:
 *    javac DerbyNetClient.java
 *
 * and run the client
 *    java -cp .:derbyclient.jar DerbyNetClient
 *
 * assuming the server has been started before.
 *
**/
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DerbyNetClient {
    private static BufferedReader br = null;

    public static void main(String[] args) {
        br = new BufferedReader(
                 new InputStreamReader(System.in));

        // Ladujemy sterownik. Odpowiedni jar powinien byc
        // w classpath, czyli na przyklad mozna dodac
        // '-cp .:/opt/java/db/lib/derbyclient.jar' do wywolania
        final String driver =
            "org.apache.derby.jdbc.ClientDriver";
            // "org.apache.derby.jdbc.EmbeddedDriver";
            // MySQL: "com.mysql.jdbc.Driver"
        try {
            Class.forName(driver);
        } catch (Exception e) {
            System.err.println(
                "K\u0142opoty ze sterownikiem " + driver);
            System.exit(1);
        }

        // Lista 'statements', zeby potem wszystkie zamknac
        ArrayList<Statement> statements =
                                new ArrayList<Statement>();

        Statement         stat = null;
        ResultSet           rs = null;

        Connection conn = null;
        try {
            // Nawiazujemy polaczenie z serwerem
            String db = "MojaBaza";
            conn = DriverManager.getConnection(
                        "jdbc:derby://localhost:1527/" + db,
                        "uzytkownik", "haslo");
            System.out.println(
                "\n Po\u0142\u0105czenie z " + db + " OK");

            pause();

            // drukujemy informacje o bazie danych
            printMetaInfo(conn);

            pause();

            // domyslnie 'autocommit' jest i tak true
            conn.setAutoCommit(true);

            stat = conn.createStatement();
            statements.add(stat);

            rs = stat.executeQuery(
                    "SELECT aut as A, tyt as T, dat as D" +
                    "    FROM Ksiazki" +
                    "    WHERE dat > \'1899-12-31\'" +
                    "    ORDER BY dat,aut"
                 );

            infoRS(rs);

            pause();

            System.out.println("\nZwr\u00f3cone rekordy:");
            while (rs.next()) {
                System.out.printf(
                    "Autor: %11s Tylu\u0142:%25s (%tY)\n",
                    rs.getString(1).trim(),
                    rs.getString("T"),
                    rs.getDate("D")
                );
            }

            // usuwamy tabele
            System.out.print(
                "\nCzy usun\u0105\u0107 tabel" +
                "\u0119 \'Ksiazki\'? (yYtT/nN) [n] ");
            String odp = null;
            try {
                odp = br.readLine().trim();
            } catch(IOException e) {
                System.exit(1);
            }
            if (odp.matches("[yYtT]")) {
                stat.execute("DROP TABLE Ksiazki");
                System.out.println(
                    "\nTabela \'Ksiazki\' usuni\u0119ta");
            } else {
                System.out.println(
                    "\nTabela \'Ksiazki\' zachowana");
            }
        }
        catch (SQLException e) {
            printSQLException(e);
        } finally {
            // Zamykamy polaczenie
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                printSQLException(e);
            }
            // Zamykamy ResultSet
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
            } catch (SQLException e) {
                printSQLException(e);
            }
            // zamykamy wszystkie statements
            for (Statement s : statements) {
                try {
                    if (s != null) {
                        s.close();
                        s = null;
                    }
                } catch (SQLException e) {
                    printSQLException(e);
                }
            }
        }
        System.out.println("--- KONIEC ---");
    }

    public static void printSQLException(SQLException e) {
        while (e != null) {
            System.err.println("Messg: " + e.getMessage());
            e = e.getNextException();
        }
        System.exit(1);
    }

    public static void printMetaInfo(Connection conn) {
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            System.out.println("\nBaza:          " +
                dbmd.getDatabaseProductName());
            System.out.println("Nr wersji:       " +
                dbmd.getDatabaseMajorVersion());
            System.out.println("Nr podwersji:    " +
                dbmd.getDatabaseMinorVersion());
            System.out.println("Sterownik:       " +
                dbmd.getDriverName());
            System.out.println("Wersja ster.:    " +
                dbmd.getDriverMajorVersion());
            System.out.println("Podwersja ster.: " +
                dbmd.getDriverMinorVersion());
            System.out.println("Transakcje:      " +
                dbmd.supportsTransactions());
        }
        catch(SQLException e) {
            printSQLException(e);
        }
    }

    private static void pause() {
        System.out.print("\nNaci\u015bnij ENTER ");
        try {
            br.readLine();
        } catch(IOException ignore){ };
    }

    private static void infoRS(ResultSet rs) {
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols =  rsmd.getColumnCount();

            System.out.println("\nRS META INFO:");
            for(int c = 0; c < cols; ++c) {
                System.out.printf("Kol %2d, Name %5s, "   +
                        "Type %2d, TypeName %s\n        " +
                        "TableName %6s, Class %s\n\n",
                    c,
                    rsmd.getColumnName(c+1),
                    rsmd.getColumnType(c+1),
                    rsmd.getColumnTypeName(c+1),
                    rsmd.getTableName(c+1),
                    rsmd.getColumnClassName(c+1));
            }
        } catch(SQLException e) {
             printSQLException(e);
        }
    }
}